package com.ced.sip.analysis.biz.impl;

import java.util.List;
import java.util.Map;

import com.ced.base.biz.impl.BaseBizImpl;
import com.ced.base.exception.BaseException;
import com.ced.base.utils.RollPage;
import com.ced.sip.analysis.biz.IPurchaseAmountBiz;
import com.ced.sip.analysis.entity.MaterialListReport;
import com.ced.sip.analysis.entity.PurchaseAmount;
import com.ced.sip.analysis.entity.PurchaseTrack;
import com.ced.sip.analysis.entity.SupplierWinning;
import com.ced.sip.common.utils.StringUtil;

public class PurchaseAmountBizImpl extends BaseBizImpl implements IPurchaseAmountBiz{

	/**
	 * 采购金额成交统计查询  按月查询
	 * @param map
	 * @param sqlStr
	 * @return
	 * @throws BaseException
	 */
	public List getPurchaseAmountBuyMonth(Map<String, Object> map,String sqlStr) throws BaseException {
		StringBuffer sql=new StringBuffer("select to_char(write_date,'mm') month,sum(t.bid_price/10000) amountMoney,count(distinct t.rc_id) totalNumber from bid_award t where t.status='0' ");
		if(StringUtil.isNotBlank(map)){
			if(StringUtil.isNotBlank(map.get("year"))){
				sql.append(" and to_char(t.write_date,'yyyy')='").append(map.get("year")).append("' ");
			}
			if(StringUtil.isNotBlank(map.get("buyWay"))){
				sql.append(" and t.buy_way = '").append(map.get("buyWay")).append("' ");
			}
		}
		sql.append(sqlStr);
		sql.append(" group by  to_char(write_date,'mm')");
		return this.getObjectsList(PurchaseAmount.class, sql.toString());
	}
	/**
	 * 采购金额成交统计查询  按季度查询
	 * @param map
	 * @param sqlStr
	 * @return
	 * @throws BaseException
	 */
	public List getPurchaseAmountBuySecond(Map<String, Object> map,String sqlStr) throws BaseException {
		StringBuffer sql=new StringBuffer("select to_char(write_date,'Q') month,sum(t.bid_price/10000) amountMoney,count(distinct t.rc_id) totalNumber from bid_award t where t.status='0' ");
		if(StringUtil.isNotBlank(map)){
			if(StringUtil.isNotBlank(map.get("year"))){
				sql.append(" and to_char(t.write_date,'yyyy')='").append(map.get("year")).append("' ");
			}
			if(StringUtil.isNotBlank(map.get("buyWay"))){
				sql.append(" and t.buy_way = '").append(map.get("buyWay")).append("' ");
			}
		}
		sql.append(sqlStr);
		sql.append(" group by  to_char(write_date,'Q')");
		return this.getObjectsList(PurchaseAmount.class, sql.toString());
	}
	/**
	 * 采购金额成交统计查询  按年度查询
	 * @param map
	 * @param sqlStr
	 * @return
	 * @throws BaseException
	 */
	public List getPurchaseAmountBuyYear(Map<String, Object> map,String sqlStr) throws BaseException {
		StringBuffer sql=new StringBuffer("select to_char(write_date,'yyyy') year,sum(t.bid_price/10000) amountMoney,count(distinct t.rc_id) totalNumber from bid_award t where t.status='0' ");
		if(StringUtil.isNotBlank(map)){
			if(StringUtil.isNotBlank(map.get("year"))){
				sql.append(" and to_char(t.write_date,'yyyy')='").append(map.get("year")).append("' ");
			}
			if(StringUtil.isNotBlank(map.get("buyWay"))){
				sql.append(" and t.buy_way = '").append(map.get("buyWay")).append("' ");
			}
		}
		sql.append(sqlStr);
		sql.append(" group by  to_char(write_date,'yyyy')");
		return this.getObjectsList(PurchaseAmount.class, sql.toString());
	}
	/**
	 * 采购整体跟踪统计
	 * @param rollPage 分页对象
	 * @param map 查询参数对象
	 * @author lgl 2017-10-21
	 * @return
	 * @throws BaseException 
	 */
	public List getPurchaseTrackList(RollPage rollPage,
			Map<String,Object> map,String sqlStr) throws BaseException {
		StringBuffer sql = new StringBuffer("select ");
		sql.append("rc_id,bid_code,buy_remark,buy_way,supplier_type,purchase_dept_id,writer,write_date,");
		sql.append("total_budget,service_status_cn,bid_status_cn,notice_publish_date,");
		sql.append("bid_return_date,bid_open_date,bid_award_date,change_num,bid_winning_price,");
		sql.append("(select count(is_id) from invite_supplier where rc_id=t.rc_id) inviteSupplierNumber,");
		sql.append("(select count(is_id) from invite_supplier where rc_id=t.rc_id and is_price_aa='0' ) priceSupplierNumber ");
		sql.append("from required_collect t where 1=1 ");
		if(StringUtil.isNotBlank(map)){			
			if(StringUtil.isNotBlank(map.get("bidCode"))){
				sql.append(" and t.bid_code like  '%").append(map.get("bidCode")).append("%'");
			}
			if(StringUtil.isNotBlank(map.get("buyRemark"))){
				sql.append(" and t.buy_remark like '%").append(map.get("buyRemark")).append("%'");
			}
			if(StringUtil.isNotBlank(map.get("writeDateStart"))){
				sql.append(" and t.write_date>=to_date('").append(map.get("writeDateStart")).append("','yyyy-MM-dd')");
			}
			if(StringUtil.isNotBlank(map.get("writeDateEnd"))){
				sql.append(" and t.write_date<=to_date('").append(map.get("writeDateEnd")).append("','yyyy-MM-dd')");
			}
		}
		if (StringUtil.isNotBlank(sqlStr)) {
			sql.append(" "+sqlStr+" ");
		}
		sql.append(" order by "+rollPage.getOrderColumn()+" "+rollPage.getOrderDir()+" ");
		
		return this.getObjectsListRollPage(PurchaseTrack.class,rollPage,sql.toString()); 
	}
	/**
	 * 采购整体跟踪统计  导出EXCEL
	 * @param map 查询参数对象
	 * @author lgl 2017-10-21
	 * @return
	 * @throws BaseException 
	 */
	public List getPurchaseTrackListAll(Map<String,Object> map,String sqlStr) throws BaseException {
		StringBuffer sql = new StringBuffer("select ");
		sql.append("rc_id,bid_code,buy_remark,buy_way,supplier_type,purchase_dept_id,writer,write_date,");
		sql.append("total_budget,service_status_cn,bid_status_cn,notice_publish_date,");
		sql.append("bid_return_date,bid_open_date,bid_award_date,change_num,bid_winning_price,");
		sql.append("(select count(is_id) from invite_supplier where rc_id=t.rc_id) inviteSupplierNumber,");
		sql.append("(select count(is_id) from invite_supplier where rc_id=t.rc_id and is_price_aa='0' ) priceSupplierNumber ");
		sql.append("from required_collect t where 1=1 ");
		if(StringUtil.isNotBlank(map)){			
			if(StringUtil.isNotBlank(map.get("bidCode"))){
				sql.append(" and t.bid_code like  '%").append(map.get("bidCode")).append("%'");
			}
			if(StringUtil.isNotBlank(map.get("buyRemark"))){
				sql.append(" and t.buy_remark like '%").append(map.get("buyRemark")).append("%'");
			}
			if(StringUtil.isNotBlank(map.get("writeDateStart"))){
				sql.append(" and t.write_date>=do_date('").append(map.get("writeDateStart")).append("','yyyy-MM-dd')");
			}
			if(StringUtil.isNotBlank(map.get("writeDateEnd"))){
				sql.append(" and t.write_date>=do_date('").append(map.get("writeDateEnd")).append("','yyyy-MM-dd')");
			}
		}
		if (StringUtil.isNotBlank(sqlStr)) {
			sql.append(" "+sqlStr+" ");
		}
		sql.append(" order by t.rc_id desc ");
		return this.getObjectsList(PurchaseTrack.class,sql.toString()); 
	}
	/**
     * 供应商采购成交统计
     * @param map
     * @param sqlStr
     * @return
     * @throws BaseException
     */
	public List getSupplierWinningList(Map<String, Object> map, String sqlStr)
			throws BaseException {
		StringBuffer sql=new StringBuffer("select * from (select ba.supplier_id,si.supplier_name,sum(ba.bid_price) amountMoney,count(rc_id) totalNumber from bid_award ba,supplier_info si  ");
		sql.append(" where ba.supplier_id=si.supplier_id and ba.status='0' ");
		if(StringUtil.isNotBlank(map)){
			if(StringUtil.isNotBlank(map.get("writeDateStart"))){
				sql.append(" and ba.write_date>=do_date('").append(map.get("writeDateStart")).append("','yyyy-MM-dd')");
			}
			if(StringUtil.isNotBlank(map.get("writeDateEnd"))){
				sql.append(" and ba.write_date>=do_date('").append(map.get("writeDateEnd")).append("','yyyy-MM-dd')");
			}
			if(StringUtil.isNotBlank(map.get("buyWay"))){
				sql.append(" and ba.buy_way = '").append(map.get("buyWay")).append("' ");
			}
		}
		sql.append(sqlStr);
		sql.append(" group by ba.supplier_id,si.supplier_name) sw order by sw.amountMoney desc ");
		return this.getObjectsList(SupplierWinning.class, sql.toString());
	}
	/**
     * 采购品成交统计排行
     * @param rollPage
     * @param map
     * @param sqlStr
     * @return
     * @throws BaseException
     */
	public List getMaterialReportList(RollPage rollPage,
			Map<String, Object> map, String sqlStr) throws BaseException {
		StringBuffer sql=new StringBuffer("select * from (select rcd.buy_code,rcd.buy_name,rcd.material_type,sum(bad.price*bad.award_amount) amountMoney,count(bad.ba_id) totalNumber from bid_award_detail bad,bid_award ba,required_collect_detail rcd  ");
		sql.append(" where bad.ba_id=ba.ba_id and bad.rcd_id=rcd.rcd_id and ba.status='0' ");
		if(StringUtil.isNotBlank(map)){
			if(StringUtil.isNotBlank(map.get("writeDateStart"))){
				sql.append(" and ba.write_date>=do_date('").append(map.get("writeDateStart")).append("','yyyy-MM-dd')");
			}
			if(StringUtil.isNotBlank(map.get("writeDateEnd"))){
				sql.append(" and ba.write_date>=do_date('").append(map.get("writeDateEnd")).append("','yyyy-MM-dd')");
			}
			if(StringUtil.isNotBlank(map.get("buyName"))){
				sql.append(" and rcd.buy_name like '%").append(map.get("buyName")).append("%' ");
			}
			if(StringUtil.isNotBlank(map.get("materialType"))){
				sql.append(" and rcd.material_type like '%").append(map.get("materialType")).append("%' ");
			}
			if(StringUtil.isNotBlank(map.get("mkCode"))){
				sql.append(" and rcd.buy_code like '%").append(map.get("mkCode")).append("%' ");
			}
		}
		sql.append(sqlStr);
		sql.append(" group by rcd.buy_code,rcd.buy_name,rcd.material_type) mr order by mr.amountMoney desc ");
		return this.getObjectsListRollPage(MaterialListReport.class,rollPage,sql.toString());
	}
	/**
     * 采购品成交统计排行
     * @param rollPage
     * @param map
     * @param sqlStr
     * @return
     * @throws BaseException
     */
	public List getMaterialReportListAll(Map<String, Object> map, String sqlStr) throws BaseException {
		StringBuffer sql=new StringBuffer("select * from (select rcd.buy_code,rcd.buy_name,rcd.material_type,sum(bad.price*bad.award_amount) amountMoney,count(bad.ba_id) totalNumber from bid_award_detail bad,bid_award ba,required_collect_detail rcd  ");
		sql.append(" where bad.ba_id=ba.ba_id and bad.rcd_id=rcd.rcd_id and ba.status='0' ");
		if(StringUtil.isNotBlank(map)){
			if(StringUtil.isNotBlank(map.get("writeDateStart"))){
				sql.append(" and ba.write_date>=do_date('").append(map.get("writeDateStart")).append("','yyyy-MM-dd')");
			}
			if(StringUtil.isNotBlank(map.get("writeDateEnd"))){
				sql.append(" and ba.write_date>=do_date('").append(map.get("writeDateEnd")).append("','yyyy-MM-dd')");
			}
			if(StringUtil.isNotBlank(map.get("buyName"))){
				sql.append(" and rcd.buy_name like '%").append(map.get("buyName")).append("%' ");
			}
			if(StringUtil.isNotBlank(map.get("materialType"))){
				sql.append(" and rcd.material_type like '%").append(map.get("materialType")).append("%' ");
			}
			if(StringUtil.isNotBlank(map.get("mkCode"))){
				sql.append(" and rcd.buy_code like '%").append(map.get("mkCode")).append("%' ");
			}
		}
		sql.append(sqlStr);
		sql.append(" group by rcd.buy_code,rcd.buy_name,rcd.material_type) mr order by mr.amountMoney desc ");
		return this.getObjectsList(MaterialListReport.class,sql.toString());
	}

}
